package com.myframework.utils.dbutil;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.myframework.utils.exceptions.WMDaoException;
import com.myframework.utils.logutil.WMLogger;

public class DatabaseUtil {

	/**
	 * Get the connection to database using a given JNDI name
	 * 
	 * @param jndiName
	 *          Used to get connection to database
	 * @return
	 */
	public static Connection getConnection(String jndiName) {
		Connection conn = null;

		// Get the connection to database
		try {
			InitialContext ic = new InitialContext();
			DataSource ds = (DataSource) ic.lookup(jndiName);
			conn = ds.getConnection();
			if (null != conn) {
				conn.setAutoCommit(true);
				conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
				WMLogger.debug("Get the database connection.");
			} else {
				throw new WMDaoException("Unable to get connection to database.");
			}
		} catch (NamingException e) {
			WMLogger.error("Error on finding JNDI name: " + jndiName + ".", e);
		} catch (SQLException e) {
			WMLogger.error("Error on getting connection to database.", e);
		} catch (WMDaoException e) {
			WMLogger.error("Unable to get connection to database.");
		}

		return conn;
	}

	/**
	 * Get the connection to database using the default JNDI name
	 * <strong>java:/comp/env/jdbc/webmeetingDB</strong>
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;

		// Get the connection to database
		try {
			InitialContext ic = new InitialContext();
			DataSource ds = (DataSource) ic.lookup("java:/comp/env/jdbc/webmeetingDB");
			conn = ds.getConnection();
			if (null != conn) {
				conn.setAutoCommit(true);
				conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
				WMLogger.debug("Get the database connection.");
			} else {
				throw new WMDaoException("Unable to get connection to database.");
			}
		} catch (NamingException e) {
			WMLogger.error("Error on finding JNDI name: " + "java:/comp/env/jdbc/webmeetingDB" + ".", e);
		} catch (SQLException e) {
			WMLogger.error("Error on getting connection to database.", e);
		} catch (WMDaoException e) {
			WMLogger.error("Unable to get connection to database.");
		}

		return conn;
	}

	/**
	 * Build the sql statement to get the last insert row id
	 * <p>
	 * Note:<br/>
	 * The result sql statement is like <strong>SELECT LAST_INSERT_ID() FROM
	 * tableName</strong>
	 * </p>
	 * 
	 * @param tableName
	 * @return
	 */
	public static String buildIdentitySelect(final String tableName) {
		return "SELECT LAST_INSERT_ID() FROM " + tableName;
	}

	/**
	 * Retrieve the meta data for the given result set
	 * 
	 * @param rs
	 *          A result set which need to be translate into an object
	 * @return
	 * @throws SQLException
	 */
	public static Map<String, Object> buildColumnMap(final ResultSet rs) throws SQLException {
		return DatabaseUtil.buildColumnMap(rs, rs.getMetaData());
	}

	/**
	 * Retrieve the meta data for the given result set
	 * 
	 * @param rs
	 *          A result set which need to be translate into an object
	 * @param rsmd
	 *          A result set meta data which contains the information of columns
	 * @return
	 * @throws SQLException
	 */
	public static Map<String, Object> buildColumnMap(final ResultSet rs, final ResultSetMetaData rsmd) throws SQLException {
		Map<String, Object> result = new HashMap<String, Object>();

		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			String thisColName = rsmd.getColumnName(i);

			Object obj = DatabaseUtil.getObject(rs, rsmd, i);
			result.put(thisColName.toUpperCase(), obj);
		}

		return result;
	}

	/**
	 * Get the object from result set
	 * 
	 * @param rs
	 *          A result set which need to be translate into an object
	 * @param rsmd
	 *          A result set meta data which contains the information of columns
	 * @param columnNumber
	 *          The Column number which column need to be translated
	 * @return
	 * @throws SQLException
	 */
	public static Object getObject(final ResultSet rs, final ResultSetMetaData rsmd, final int columnNumber) throws SQLException {
		int columnType = rsmd.getColumnType(columnNumber);
		Object result = rs.getObject(columnNumber);

		if (null != result) {
			// Check for CLOB
			if (java.sql.Types.CLOB == columnType) {
				Clob clob = (Clob) result;
				long length = clob.length();
				if (0 == length) {
					result = "";
				} else {
					result = clob.getSubString(1, (int) length);
				}
			} else if (java.sql.Types.BLOB == columnType) {
				result = rs.getBlob(columnNumber);
			} else if (java.sql.Types.BIGINT == columnType || java.sql.Types.TINYINT == columnType || java.sql.Types.SMALLINT == columnType
					|| java.sql.Types.INTEGER == columnType) {
				result = Long.valueOf(result.toString());
			} else if (java.sql.Types.DECIMAL == columnType || java.sql.Types.FLOAT == columnType || java.sql.Types.REAL == columnType
					|| java.sql.Types.DOUBLE == columnType) {
				result = Double.valueOf(result.toString());
			} else if (java.sql.Types.NUMERIC == columnType) {
				int scale = rsmd.getScale(columnNumber);
				if (scale > 0) {
					result = Double.valueOf(result.toString());
				} else {
					result = Long.valueOf(result.toString());
				}
			}
		}

		return result;
	}
}
